IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_List_stp_department1')
          AND TYPE IN ('P','PC'))
DROP PROCEDURE USP_List_stp_department1

GO
/****** Object:  StoredProcedure [dbo].[USP_List_stp_department1]    Script Date: 12/20/2010 16:00:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		JOE
-- Create date:  27/11/2553
-- [USP_List_stp_department1] '16',5
-- [USP_List_stp_department] '15',3
-- =============================================
CREATE PROCEDURE [dbo].[USP_List_stp_department1]
	@department_code nvarchar(max),
	@master_department_code int
	--@department_status int
AS
BEGIN
		DECLARE @department Varchar(10)
	DECLARE @table as table(department_code int ,
							department_number varchar(200),
							department_name varchar(500),
							department_status tinyint,
							master_department_code int,
							master_department_name varchar(500),
							fullname Varchar(1000)
							)
	
	DECLARE depart_list_cursor CURSOR FOR 	
	select 
			number
	from
	dbo.fnIter_intlist_to_table(@department_code)
	Order by number
	
	OPEN  depart_list_cursor
	FETCH NEXT FROM depart_list_cursor 
	INTO @department
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
	
			IF @department='0' 
			BEGIN
				SELECT @department = department_code 
				FROM stp_department 
				WHere department_code > 0
				and department_parent is null
			END
			
			IF @master_department_code = 0 
			BEGIN
				INSERT INTO @table
				
				SELECT department_code,department_number, department_name, department_status
					,  s.master_department_code 
					, d.master_department_name 
					, CASE WHEN master_department_level=1 THEN department_name 
						ELSE  dbo.UFN_GetHirachyPrefix2(d.master_department_level) + department_name END as fullname 
				FROM stp_department s
				JOIN master_department d on d.master_department_code = s.master_department_code
				where department_code =@department-- @department_code
				
				UNION ALL
				
				SELECT * 
				FROM dbo.fn_GetParentName(@department,@master_department_code)
			END
			ELSE
			BEGIN
				IF (SELECT COUNT(*) 
					FROM dbo.fn_GetParentName(@department,@master_department_code)) > 0 
				BEGIN
					INSERT INTO @table
					
					SELECT department_code,department_number, department_name, department_status
					,  s.master_department_code 
					, d.master_department_name 
					, CASE WHEN master_department_level=1 THEN department_name 
						ELSE  dbo.UFN_GetHirachyPrefix2(d.master_department_level) + department_name END as fullname 
					FROM stp_department s
					JOIN master_department d on d.master_department_code = s.master_department_code
					where department_code= @department --@department_code
					
					UNION ALL
					
					SELECT * 
					FROM dbo.fn_GetParentName(@department,@master_department_code)
				END
				ELSE
				BEGIn
					INSERT INTO @table
					
					SELECT department_code,department_number, department_name, department_status
					,  s.master_department_code 
					, d.master_department_name 
					, CASE WHEN master_department_level=1 THEN department_name 
						ELSE  dbo.UFN_GetHirachyPrefix2(d.master_department_level) + department_name END as fullname 
					FROM stp_department s
					JOIN master_department d on d.master_department_code = s.master_department_code
					where 1=2
				END
			END
	 FETCH NEXT FROM depart_list_cursor 
	 INTO @department
	
	END 
	CLOSE depart_list_cursor
	DEALLOCATE depart_list_cursor	
	--WHERE @master_department_code IN (0,masterdepartmentcode) 
	
	SELECT * FROM @table
END
